﻿using System;
using System.Collections.Generic;
using System.Linq;
// Krzysztof Heim
// pktools.codeplex.com

namespace PKTools.Database.Generator
{
    class MsSqlGenerator : Generator
    {
        readonly string[]
            excludedObjects = { "sysdiagrams" };

        public MsSqlGenerator(string excludeColumns = "") 
            : base(excludeColumns)
        {
        }

        protected override void Load(string tablePrefix, string connection)
        {
            using (this.sql = new MsSql(connection))
            {
                var
                    types = sql.LoadDictionary<int, string>("SELECT user_type_id, name FROM sys.types");

                this.tables = new List<Table>();

                var
                    temp = sql.LoadList(
                    "SELECT object_id, name, type FROM sys.objects WHERE type IN ('U', 'V')", // user tables or views
                    e => new Table
                    {
                        ID = (int)e["object_id"],
                        Code = (string)e["name"],
                        IsTable = e["type"].ToString().Trim() == "U"
                    });

                this.tables = temp.Where(e => e.Code.StartsWith(tablePrefix) && excludedObjects.All(t => t != e.Code)).ToList();

                foreach (var table in this.tables)
                {
                    sql.Clear();
                    sql.AddInput("ID", table.ID);

                    dynamic
                        cols = sql.LoadDynamicList(@"
                            SELECT 
	                            c.name, c.user_type_id, c.system_type_id, c.is_nullable, c.is_identity, ISNULL(temp.object_id, 0) is_pk
                            FROM 
	                            sys.columns c
	                            LEFT JOIN (
		                            SELECT	
			                            ic.object_id, ic.column_id
		                            FROM
			                            sys.index_columns ic
			                            JOIN sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
		                            WHERE
			                            i.is_primary_key = 1) temp ON temp.object_id = c.object_id AND temp.column_id = c.column_id
                            WHERE 
	                            c.object_id = @ID");

                    int
                        id = 0;
                    foreach (var c in cols)
                    {
                        if (c.name == this.stampColumn)
                        {
                            table.IsStamped = true;
                            continue;
                        }

                        if (this.excludeColumns.Contains(c.name))
                        {
                            if (!c.is_nullable)
                                throw new Exception("can't exclude nullable column");

                            continue;
                        }                            

                        table.Columns.Add(new Column
                        {
                            ID = id++,
                            Code = c.name,
                            CSharpType = GetCSharpType(types[c.user_type_id]),
                            IsPK = c.is_pk != 0,
                            IsNull = c.is_nullable,
                            IsIdentity = c.is_identity
                        });
                    }
                }
            }            
        }

        public static Type ToCSharpType(string sqlType)
        {
            switch (sqlType)
            {
                case "tinyint":
                    return typeof(byte);
                case "smallint":
                    return typeof(Int16);
                case "int":
                    return typeof(Int32);
                case "bigint":
                    return typeof(Int64);
                case "decimal":
                    return typeof(decimal);
                case "float":
                    return typeof(double);
                case "varchar":
                case "nvarchar":
                case "text":
                case "ntext":
                    return typeof(string);
                case "bit":
                    return typeof(bool);
                case "time":
                    return typeof(TimeSpan);
                case "date":
                case "smalldatetime":
                case "datetime":
                    return typeof(DateTime);
                case "varbinary":
                    return typeof(byte[]);
                case "uniqueidentifier":
                    return typeof(Guid);
                default:
                    return typeof(string);
            }
        }

        protected override Type GetCSharpType(string sqlType)
        {
            return ToCSharpType(sqlType);
        }
    }
}
